drop table jms_dm.dm_tab_scan_unpacking_piece_detail_dt;
CREATE TABLE jms_dm.dm_tab_scan_unpacking_piece_detail_dt
(
  `format_scan_time` datetime NULL COMMENT '格式化日期',
  `package_code` varchar(50) NULL COMMENT '包号',
  `scan_user_code` varchar(50) NULL COMMENT '扫描员code',
  `send_site_code` varchar(50) NULL COMMENT '寄件网点code',
  `shipment_no` varchar(50) NULL COMMENT '任务号',
  `network_code` varchar(50) NULL COMMENT '网点code',
  `waybill_no` varchar(100) NULL COMMENT '运单号',
  `organization_code` varchar(50) NULL COMMENT '中心组织code',
  `organization_name` varchar(200) NULL COMMENT '中心组织name',
  `scan_type` varchar(50) NULL COMMENT '扫描类型',
  `scan_time` datetime NULL COMMENT '扫描时间',
  `network_name` varchar(200) NULL COMMENT '网点名',
  `network_type` tinyint(4) NULL COMMENT '网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
  `pre_site_code` varchar(50) NULL COMMENT '上一站code',
  `pre_site_name` varchar(200) NULL COMMENT '上一站',
  `next_site_code` varchar(50) NULL COMMENT '下一站code',
  `next_site_name` varchar(200) NULL COMMENT '下一站',
  `shipment_name` varchar(300) NULL COMMENT '任务名称',
  `scan_next_pre_code` varchar(50) NULL COMMENT '扫描原表上下一站code',
  `scan_next_pre_name` varchar(200) NULL COMMENT '扫描原表上下一站',
  `scan_user` varchar(50) NULL COMMENT '扫描员',
  `scan_user_id` varchar(50) NULL COMMENT '扫描员id',
  `scan_user_type` varchar(50) NULL COMMENT '扫描员员工属性1临时工/2正式工/3小时工(已废弃)/4职能/5环节外包',
  `send_site` varchar(50) NULL COMMENT '寄件网点',
  `pcs` bigint(20) NULL COMMENT '件数',
  `weight` decimal64(10, 2) NULL COMMENT '重量',
  `upload_time` datetime NULL COMMENT '上传时间',
  `current_send_package_code` varchar(50) NULL COMMENT '当前发件包号',
  `current_send_scan_time` varchar(50) NULL COMMENT '当前发件扫描时间',
  `supplier_id` varchar(50) NULL COMMENT '供应商id',
  `supplier_code` varchar(50) NULL COMMENT '供应商编码',
  `supplier_name` varchar(300) NULL COMMENT '供应商名',
  `goods_type_id` varchar(50) NULL COMMENT '物品类型id',
  `goods_type_code` varchar(50) NULL COMMENT '物品类型code',
  `goods_type_name` varchar(200) NULL COMMENT '物品类型name',
  `is_parts` tinyint(4) NULL COMMENT '是否散件 1是 0 否',
  `is_scans` tinyint(4) NULL COMMENT '是否扫描件数 1是 0 否',
  `hours` varchar(15) NULL COMMENT '小时',
  `date_time` date NULL COMMENT 'sr分区'
) ENGINE = OLAP DUPLICATE KEY(`format_scan_time`,`package_code`, `scan_user_code`, `send_site_code`, `shipment_no` )
COMMENT '计件明细-拆包扫描'
PARTITION BY RANGE(`date_time`)
(START ('2023-09-01') END ('2023-09-30') EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(`network_code`) BUCKETS 3
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-33",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "3",
"in_memory" = "false",
"storage_format" = "V2"
);